/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.ntb.models.impl;

import com.ntb.entities.Users;
import com.ntb.common.DatabaseConnection;
import com.ntb.models.UsersDAO;
import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author DATBP
 */
public class UsersDAOImpl extends DatabaseConnection implements UsersDAO {

    public Iterator<Users> getList(String username, int groupId, int active, int page) {
        try {
            ArrayList arr = new ArrayList();
            openConnection();
            CallableStatement cst = conn.prepareCall("{call spu_pageUsers(?, ?, ?, ?, ?)}");
            cst.setString(1, username);
            cst.setInt(2, groupId);
            cst.setInt(3, active);
            cst.setInt(4, page);
            cst.setInt(5, itemsPerPage);
            ResultSet rs = cst.executeQuery();
            while (rs.next()) {
                Users user = new Users();
                user.setUserID(rs.getInt("UserID"));
                user.setGroupID(rs.getInt("GroupID"));
                user.setGroupName(rs.getString("GroupName"));
                user.setUserName(rs.getString("UserName"));
                user.setEmail(rs.getString("Email"));
                user.setFullName(rs.getString("FullName"));
                user.setActive(rs.getInt("Active"));
                user.setCreatedDate(rs.getTimestamp("CreatedDate"));
                arr.add(user);
            }
            closeConnection();
            if (arr.size() > 0) {
                return arr.iterator();
            }
        } catch (SQLException ex) {
            Logger.getLogger(UsersDAOImpl.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }

    public int getPageCount(String username, int groupId, int active) {
        try {
            openConnection();
            CallableStatement cst = conn.prepareCall("{call spu_countPageUsers(?, ?, ?, ?)}");
            cst.setString(1, username);
            cst.setInt(2, groupId);
            cst.setInt(3, active);
            cst.setInt(4, itemsPerPage);
            ResultSet rs = cst.executeQuery();
            int count = 0;
            if (rs.next()) {
                count = rs.getInt("Count");
            }
            closeConnection();

            return count;
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return 0;
    }

    public Users getRow(int id) {
        try {
            Users user = new Users();
            openConnection();
            PreparedStatement pst = conn.prepareStatement("select Users.*, GroupName from Users " +
                    "LEFT JOIN UserGroups " +
                    "ON Users.GroupID = UserGroups.GroupID " +
                    "where UserID = ?");
            pst.setInt(1, id);
            ResultSet rs = pst.executeQuery();
            if (rs.next()) {
                user.setUserID(rs.getInt("UserID"));
                user.setGroupID(rs.getInt("GroupID"));
                user.setGroupName(rs.getString("GroupName"));
                user.setUserName(rs.getString("Username"));
                user.setEmail(rs.getString("Email"));
                user.setFullName(rs.getString("FullName"));
                user.setGender(rs.getInt("Gender"));
                user.setPassword(rs.getString("Password"));
                user.setRememberCode(rs.getString("RememberCode"));
                user.setNewpassword(rs.getString("NewPassword"));
                user.setForgottenPasswordCode(rs.getString("ForgottenPasswordCode"));
                user.setActive(rs.getInt("Active"));
                user.setCreatedDate(rs.getTimestamp("CreatedDate"));
                closeConnection();
                return user;
            }
        } catch (SQLException ex) {
            Logger.getLogger(UsersDAOImpl.class.getName()).log(Level.SEVERE, null, ex);
        }
        closeConnection();
        return null;
    }

    public Users getUser(String username) {
        try {
            Users user = new Users();
            openConnection();
            PreparedStatement pst = conn.prepareStatement("select * from Users where Username=?");
            pst.setString(1, username);
            ResultSet rs = pst.executeQuery();
            if (rs.next()) {
                user.setUserID(rs.getInt("UserID"));
                user.setGroupID(rs.getInt("GroupID"));
                user.setUserName(rs.getString("Username"));
                user.setEmail(rs.getString("Email"));
                user.setFullName(rs.getString("FullName"));
                user.setGender(rs.getInt("Gender"));
                user.setPassword(rs.getString("Password"));
                user.setRememberCode(rs.getString("RememberCode"));
                user.setNewpassword(rs.getString("NewPassword"));
                user.setForgottenPasswordCode(rs.getString("ForgottenPasswordCode"));
                user.setActive(rs.getInt("Active"));
                user.setCreatedDate(rs.getTimestamp("CreatedDate"));
                closeConnection();
                return user;
            }
        } catch (SQLException ex) {
            Logger.getLogger(UsersDAOImpl.class.getName()).log(Level.SEVERE, null, ex);
        }
        closeConnection();
        return null;
    }

    public int login(String username, String password) {
        try {
            openConnection();
            PreparedStatement pst = conn.prepareStatement("select UserID from Users where Username=? AND Password = ? AND Active = 1");
            pst.setString(1, username);
            pst.setString(2, password);
            ResultSet rs = pst.executeQuery();
            if (rs.next()) {
                int userId = rs.getInt("UserID");
                closeConnection();

                return userId;
            }
        } catch (SQLException ex) {
            Logger.getLogger(UsersDAOImpl.class.getName()).log(Level.SEVERE, null, ex);
        }
        closeConnection();
        return -1;
    }

    public int updateRememberCode(String rememberCode, int userId) {
        try {
            openConnection();
            String sql = "update Users set RememberCode=?" + " where UserID=?";
            PreparedStatement pst = conn.prepareStatement(sql);
            pst.setString(1, rememberCode);
            pst.setInt(2, userId);
            int rs = pst.executeUpdate();
            closeConnection();
            return rs;
        } catch (SQLException ex) {
            Logger.getLogger(UsersDAOImpl.class.getName()).log(Level.SEVERE, null, ex);
        }
        return -1;
    }

    public int checkRememberCode(String rememberCode, int userId) {
        try {
            openConnection();
            PreparedStatement pst = conn.prepareStatement("select UserID from Users where UserID=? AND RememberCode = ? AND Active = 1");
            pst.setInt(1, userId);
            pst.setString(2, rememberCode);
            ResultSet rs = pst.executeQuery();
            if (rs.next()) {
                closeConnection();
                return userId;
            }
        } catch (SQLException ex) {
            Logger.getLogger(UsersDAOImpl.class.getName()).log(Level.SEVERE, null, ex);
        }
        closeConnection();
        return -1;
    }

    public int updatePassword(int userId) {
        try {
            openConnection();
            String sql = "update Users set Password=NewPassword" + " where UserID=?";
            PreparedStatement pst = conn.prepareStatement(sql);
            pst.setInt(1, userId);
            int rs = pst.executeUpdate();
            closeConnection();
            return rs;
        } catch (SQLException ex) {
            Logger.getLogger(UsersDAOImpl.class.getName()).log(Level.SEVERE, null, ex);
        }
        return -1;
    }

    public int updatePassword(String password, int userId) {
        try {
            openConnection();
            String sql = "update Users set Password=?" + " where UserID=?";
            PreparedStatement pst = conn.prepareStatement(sql);
            pst.setString(1, password);
            pst.setInt(2, userId);
            int rs = pst.executeUpdate();
            closeConnection();
            return rs;
        } catch (SQLException ex) {
            Logger.getLogger(UsersDAOImpl.class.getName()).log(Level.SEVERE, null, ex);
        }
        return -1;
    }

    public int updateForgotPassword(String newPassword, String forgotPasswordCode, int userId) {
        try {
            openConnection();
            String sql = "update Users set NewPassword=?, ForgottenPasswordCode=?" + " where UserID=?";
            PreparedStatement pst = conn.prepareStatement(sql);
            pst.setString(1, newPassword);
            pst.setString(2, forgotPasswordCode);
            pst.setInt(3, userId);
            int rs = pst.executeUpdate();
            closeConnection();
            return rs;
        } catch (SQLException ex) {
            Logger.getLogger(UsersDAOImpl.class.getName()).log(Level.SEVERE, null, ex);
        }
        return -1;
    }

    public int checkForgotPasswordCode(String forgotPasswordCode, int userId) {
        try {
            openConnection();
            PreparedStatement pst = conn.prepareStatement("select UserID from Users where UserID=? AND ForgottenPasswordCode = ? AND Active = 1");
            pst.setInt(1, userId);
            pst.setString(2, forgotPasswordCode);
            ResultSet rs = pst.executeQuery();
            if (rs.next()) {
                closeConnection();
                return userId;
            }
        } catch (SQLException ex) {
            Logger.getLogger(UsersDAOImpl.class.getName()).log(Level.SEVERE, null, ex);
        }
        closeConnection();
        return -1;
    }

    public int add(Users object) {
        try {
            openConnection();
            String sql = "insert Users(GroupID, UserName, Email, FullName, Gender, Password, Active, CreatedDate)" +
                    " values(?, ?, ?, ?, ?, ?, ?, ?)";
            PreparedStatement pst = conn.prepareStatement(sql);
            pst.setInt(1, object.getGroupID());
            pst.setString(2, object.getUserName());
            pst.setString(3, object.getEmail());
            pst.setString(4, object.getFullName());
            pst.setInt(5, object.getGender());
            pst.setString(6, object.getPassword());
            pst.setInt(7, object.getActive());
            pst.setTimestamp(8, new Timestamp(new java.util.Date().getTime()));
            int rs = pst.executeUpdate();
            closeConnection();
            return rs;
        } catch (SQLException ex) {
            Logger.getLogger(UsersDAOImpl.class.getName()).log(Level.SEVERE, null, ex);
        }
        return -1;
    }

    public int update(Users object) {
        try {
            openConnection();
            String sql = "update Users set GroupID=?, Username=?, " +
                    "Email=?, FullName=?, Gender=?, Password=?, Active=? where UserID=?";
            PreparedStatement pst = conn.prepareStatement(sql);
            pst.setInt(1, object.getGroupID());
            pst.setString(2, object.getUserName());
            pst.setString(3, object.getEmail());
            pst.setString(4, object.getFullName());
            pst.setInt(5, object.getGender());
            pst.setString(6, object.getPassword());
            pst.setInt(7, object.getActive());
            pst.setInt(8, object.getUserID());
            int rs = pst.executeUpdate();
            closeConnection();
            return rs;
        } catch (SQLException ex) {
            Logger.getLogger(UsersDAOImpl.class.getName()).log(Level.SEVERE, null, ex);
        }
        return -1;
    }

    public int delete(int id) {
        try {
            openConnection();
            String sql = "delete Users where UserID=?";
            PreparedStatement pst = conn.prepareStatement(sql);
            pst.setInt(1, id);
            int rs = pst.executeUpdate();
            closeConnection();
            return rs;
        } catch (SQLException ex) {
            Logger.getLogger(UsersDAOImpl.class.getName()).log(Level.SEVERE, null, ex);
        }
        return -1;
    }

    public int active(int id) {
        try {
            openConnection();
            String sql = "UPDATE Users SET Active = 1 where UserID=? AND (Active <= 0 OR Active IS NULL)";
            PreparedStatement pst = conn.prepareStatement(sql);
            pst.setInt(1, id);
            int rs = pst.executeUpdate();
            closeConnection();
            return rs;
        } catch (SQLException ex) {
            Logger.getLogger(UsersDAOImpl.class.getName()).log(Level.SEVERE, null, ex);
        }
        return -1;
    }

    public int checkUsername(String username) {
        try {
            openConnection();
            PreparedStatement pst = conn.prepareStatement("select UserID from Users where UserName = ?");
            pst.setString(1, username);
            ResultSet rs = pst.executeQuery();
            if (rs.next()) {
                closeConnection();
                return 1;
            }
        } catch (SQLException ex) {
            Logger.getLogger(UsersDAOImpl.class.getName()).log(Level.SEVERE, null, ex);
        }
        closeConnection();
        return -1;
    }

    public int checkEmail(String email) {
        try {
            openConnection();
            PreparedStatement pst = conn.prepareStatement("select UserID from Users where Email = ?");
            pst.setString(1, email);
            ResultSet rs = pst.executeQuery();
            if (rs.next()) {
                closeConnection();
                return 1;
            }
        } catch (SQLException ex) {
            Logger.getLogger(UsersDAOImpl.class.getName()).log(Level.SEVERE, null, ex);
        }
        closeConnection();
        return -1;
    }
}
